﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using TicketReservationSystem.Common.Entities;
using TicketReservationSystem.Common.Interfaces;

namespace TicketReservationSystem.DataAccess
{
    public class DataAccess : IDataAccess
    {
        private readonly SqlConnection connection;

        public DataAccess(string connectionString)
        {
            connection = new SqlConnection(connectionString);
        }

        #region Flight Management

        public bool Exists(City city)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, city.Name);
            command.CommandText = "SELECT * FROM Cities WHERE name = @name";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return result;
        }

        public bool Exists(Airport airport)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, airport.Name);
            AddSqlParameter(command, "@idCity", SqlDbType.Int, airport.City.ID);
            command.CommandText = "SELECT * FROM Airports WHERE name = @name AND idCity = @idCity";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return result;
        }

        public bool Exists(Airline airline)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, airline.Name);
            command.CommandText = "SELECT * FROM Airlines WHERE name = @name";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return result;
        }

        public bool Exists(Plane plane)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, plane.Name);
            AddSqlParameter(command, "@idAirline", SqlDbType.Int, plane.Airline.ID);
            command.CommandText = "SELECT * FROM Planes WHERE name = @name AND idAirline = @idAirline";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return result;
        }

        public bool Exists(Flight flight)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@number", SqlDbType.Int, flight.Number);
            command.CommandText = "SELECT * FROM Flights WHERE number = @number";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return result;
        }

        public bool Exists(Customer customer)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@email", SqlDbType.VarChar, customer.Email);
            command.CommandText = "SELECT * FROM Flights WHERE email = @email";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return result;
        }

        public bool Exists(Reservation reservation)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@idCustomer", SqlDbType.Int, reservation.Customer.ID);
            AddSqlParameter(command, "@idFlight", SqlDbType.Int, reservation.Flight.ID);
            command.CommandText = "SELECT * FROM Reservations WHERE idCustomer = @idCustomer AND idFlight = @idFlight";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return result;
        }

        public void Add(City city)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, city.Name);
            AddSqlParameter(command, "@latitude", SqlDbType.Int, city.Latitude);
            AddSqlParameter(command, "@longitude", SqlDbType.Int, city.Longitude);

            command.CommandText = "INSERT INTO Cities (name, latitude, longitude) VALUES (@name, @latitude, @longitude)";
            command.ExecuteReader();

            connection.Close();
        }

        public void Add(Airport airport)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, airport.Name);
            AddSqlParameter(command, "@idCity", SqlDbType.Int, airport.City.ID);

            command.CommandText = "INSERT INTO Airports (name, idCity) VALUES (@name, @idCity)";
            command.ExecuteReader();

            connection.Close();
        }

        public void Add(Airline airline)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, airline.Name);

            command.CommandText = "INSERT INTO Airlines (name) VALUES (@name)";
            command.ExecuteReader();

            connection.Close();
        }

        public void Add(Plane plane)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, plane.Name);
            AddSqlParameter(command, "@capacity", SqlDbType.Int, plane.Capacity);
            AddSqlParameter(command, "@idAirline", SqlDbType.Int, plane.Airline.ID);

            command.CommandText = "INSERT INTO Planes (name, capacity, idAirline) VALUES (@name, @capacity, @idAirline)";
            command.ExecuteReader();

            connection.Close();
        }

        public void Add(Flight flight)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@number", SqlDbType.Int, flight.Number);
            AddSqlParameter(command, "@idPlane", SqlDbType.Int, flight.Plane.ID);
            AddSqlParameter(command, "@idDepartureAirport", SqlDbType.Int, flight.Departure.ID);
            AddSqlParameter(command, "@idDestinationAirport", SqlDbType.Int, flight.Destination.ID);
            AddSqlParameter(command, "@departureTime", SqlDbType.DateTime, flight.DepartureTime);
            AddSqlParameter(command, "@arrivalTime", SqlDbType.DateTime, flight.ArrivalTime);
            AddSqlParameter(command, "@price", SqlDbType.Float, flight.Price);

            command.CommandText = "INSERT INTO Flights (number, idPlane, idDepartureAirport, idDestinationAirport, departureTime, arrivalTime, price) VALUES (@number, @idPlane, @idDepartureAirport, @idDestinationAirport, @departureTime, @arrivalTime, @price)";
            command.ExecuteReader();

            connection.Close();
        }

        public void Add(Customer customer)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@firstname", SqlDbType.VarChar, customer.FirstName);
            AddSqlParameter(command, "@lastname", SqlDbType.VarChar, customer.LastName);
            AddSqlParameter(command, "@email", SqlDbType.VarChar, customer.Email);

            command.CommandText = "INSERT INTO Customers (firstname, lastname, email) VALUES (@firstname, @lastname, @email)";
            command.ExecuteReader();

            connection.Close();
        }

        public void Add(Reservation reservation)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@idCustomer", SqlDbType.Int, reservation.Customer.ID);
            AddSqlParameter(command, "@idFlight", SqlDbType.Int, reservation.Flight.ID);
            AddSqlParameter(command, "@date", SqlDbType.DateTime, DateTime.Now);

            command.CommandText = "INSERT INTO Reservation (idCustomer, idFlight, date) VALUES (@idCustomer, @idFlight, @date)";
            command.ExecuteReader();

            connection.Close();
        }

        public void Remove(City city)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@id", SqlDbType.Int, city.ID);

            command.CommandText = "DELETE FROM Cities WHERE id = @id";
            command.ExecuteReader();

            connection.Close();
        }

        public void Remove(Airport airport)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@id", SqlDbType.Int, airport.ID);

            command.CommandText = "DELETE FROM Airports WHERE id = @id";
            command.ExecuteReader();

            connection.Close();
        }

        public void Remove(Airline airline)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@id", SqlDbType.Int, airline.ID);

            command.CommandText = "DELETE FROM Airlines WHERE id = @id";
            command.ExecuteReader();

            connection.Close();
        }

        public void Remove(Plane plane)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@id", SqlDbType.Int, plane.ID);

            command.CommandText = "DELETE FROM Planes WHERE id = @id";
            command.ExecuteReader();

            connection.Close();
        }

        public void Remove(Flight flight)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@id", SqlDbType.Int, flight.ID);

            command.CommandText = "DELETE FROM Flights WHERE id = @id";
            command.ExecuteReader();

            connection.Close();
        }

        public void Remove(Customer customer)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@id", SqlDbType.Int, customer.ID);

            command.CommandText = "DELETE FROM Customers WHERE id = @id";
            command.ExecuteReader();

            connection.Close();
        }

        public void Remove(Reservation reservation)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@id", SqlDbType.Int, reservation.ID);

            command.CommandText = "DELETE FROM Reservations WHERE id = @id";
            command.ExecuteReader();

            connection.Close();
        }

        public void RemoveAllRelatedFlights(Airport airport)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@idAirport", SqlDbType.Int, airport.ID);

            command.CommandText = "DELETE FROM Flights WHERE idDepartureAirport = @idAirport OR idDestinationAirport = @idAirport";
            command.ExecuteReader();

            connection.Close();
        }

        public void RemoveAllRelatedFlights(Plane plane)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@idPlane", SqlDbType.Int, plane.ID);

            command.CommandText = "DELETE FROM Flights WHERE idPlane = @idPlane";
            command.ExecuteReader();

            connection.Close();
        }

        public void RemoveAllRelatedReservations(Flight flight)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@idFlight", SqlDbType.Int, flight.ID);

            command.CommandText = "DELETE FROM Reservation WHERE idFlight = @idFlight";
            command.ExecuteReader();

            connection.Close();
        }

        public void RemoveAllRelatedReservations(Customer customer)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@idCustomer", SqlDbType.Int, customer.ID);

            command.CommandText = "DELETE FROM Reservation WHERE idCustomer = @idCustomer";
            command.ExecuteReader();

            connection.Close();
        }

        public City GetCityByID(int id)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Cities WHERE id = @id";

            var city = new City();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        city.ID = reader.GetInt32(0);
                        city.Name = reader.GetString(1);
                        city.Latitude = reader.GetInt32(2);
                        city.Longitude = reader.GetInt32(3);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return city;
        }

        public Airport GetAirportByID(int id)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Airports WHERE id = @id";

            var idCity = 0;
            var airport = new Airport();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        airport.ID = reader.GetInt32(0);
                        airport.Name = reader.GetString(1);
                        idCity = reader.GetInt32(2);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            airport.City = GetCityByID(idCity);

            return airport;
        }

        public Airline GetAirlineByID(int id)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Airlines WHERE id = @id";

            var airline = new Airline();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        airline.ID = reader.GetInt32(0);
                        airline.Name = reader.GetString(1);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return airline;
        }

        public Plane GetPlaneByID(int id)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Planes WHERE id = @id";

            var idAirline = 0;
            var plane = new Plane();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        plane.ID = reader.GetInt32(0);
                        plane.Name = reader.GetString(1);
                        plane.Capacity = reader.GetInt32(2);
                        idAirline = reader.GetInt32(3);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            plane.Airline = GetAirlineByID(idAirline);

            return plane;
        }

        public Flight GetFlightByID(int id)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Flights WHERE id = @id";

            var idPlane = 0;
            var idDepartureAirport = 0;
            var idDestinationAirport = 0;
            var flight = new Flight();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        flight.ID = reader.GetInt32(0);
                        flight.Number = reader.GetInt32(1);
                        idPlane = reader.GetInt32(2);
                        idDepartureAirport = reader.GetInt32(3);
                        idDestinationAirport = reader.GetInt32(4);
                        flight.DepartureTime = reader.GetDateTime(5);
                        flight.ArrivalTime = reader.GetDateTime(6);
                        flight.Price = reader.GetFloat(7);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            flight.Plane = GetPlaneByID(idPlane);
            flight.Departure = GetAirportByID(idDepartureAirport);
            flight.Destination = GetAirportByID(idDestinationAirport);

            return flight;
        }

        public Customer GetCustomerByID(int id)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Customers WHERE id = @id";

            var customer = new Customer();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        customer.ID = reader.GetInt32(0);
                        customer.FirstName = reader.GetString(1);
                        customer.LastName = reader.GetString(2);
                        customer.Email = reader.GetString(3);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return customer;
        }

        public Reservation GetReservationByID(int id)
        {
            connection.Open();

            var command = connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Reservations WHERE id = @id";

            var idCustomer = 0;
            var idFlight = 0;
            var reservation = new Reservation();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        reservation.ID = reader.GetInt32(0);
                        idCustomer = reader.GetInt32(1);
                        idFlight = reader.GetInt32(2);
                        reservation.Date = reader.GetDateTime(3);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            reservation.Customer = GetCustomerByID(idCustomer);
            reservation.Flight = GetFlightByID(idFlight);

            return reservation;
        }

        public IList<City> AllCities()
        {
            connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM Cities";

            var cities = new List<City>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        var city = new City
                        {
                            ID = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Latitude = reader.GetInt32(2),
                            Longitude = reader.GetInt32(3)
                        };

                        cities.Add(city);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return cities;
        }

        public IList<Airport> AllAirports()
        {
            connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM Airports";

            var airports = new List<Airport>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        airports.Add(new Airport { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            for (var index = 0; index < airports.Count; index++)
            {
                airports[index] = GetAirportByID(airports[index].ID);
            }

            return airports;
        }

        public IList<Airline> AllAirlines()
        {
            connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM Airlines";

            var airlines = new List<Airline>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        var airline = new Airline
                        {
                            ID = reader.GetInt32(0),
                            Name = reader.GetString(1)
                        };

                        airlines.Add(airline);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return airlines;
        }

        public IList<Plane> AllPlanes()
        {
            connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM Planes";

            var planes = new List<Plane>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        planes.Add(new Plane { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            for (var index = 0; index < planes.Count; index++)
            {
                planes[index] = GetPlaneByID(planes[index].ID);
            }

            return planes;
        }

        public IList<Flight> AllFlights()
        {
            connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM Flights";

            var flights = new List<Flight>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        flights.Add(new Flight { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            for (var index = 0; index < flights.Count; index++)
            {
                flights[index] = GetFlightByID(flights[index].ID);
            }

            return flights;
        }

        public IList<Customer> AllCustomers()
        {
            connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM Customers";

            var customers = new List<Customer>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        var customer = new Customer
                        {
                            ID = reader.GetInt32(0),
                            FirstName = reader.GetString(1),
                            LastName = reader.GetString(2),
                            Email = reader.GetString(3)
                        };

                        customers.Add(customer);
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            return customers;
        }

        public IList<Reservation> AllReservations()
        {
            connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM Reservations";

            var reservations = new List<Reservation>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        reservations.Add(new Reservation { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            for (var index = 0; index < reservations.Count; index++)
            {
                reservations[index] = GetReservationByID(reservations[index].ID);
            }

            return reservations;
        }

        public IList<Airport> AllAirportsInCity(City city)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@idCity", SqlDbType.Int, city.ID);
            command.CommandText = "SELECT * FROM Airports WHERE idCity = @idCity";

            var airports = new List<Airport>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        airports.Add(new Airport { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            for (var index = 0; index < airports.Count; index++)
            {
                airports[index] = GetAirportByID(airports[index].ID);
            }

            return airports;
        }

        public IList<Plane> AllPlanesInAirline(Airline airline)
        {
            connection.Open();

            var command = connection.CreateCommand();
            AddSqlParameter(command, "@idAirline", SqlDbType.Int, airline.ID);
            command.CommandText = "SELECT * FROM Planes WHERE idAirline = @idAirline";

            var planes = new List<Plane>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        planes.Add(new Plane { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }

            for (var index = 0; index < planes.Count; index++)
            {
                planes[index] = GetPlaneByID(planes[index].ID);
            }

            return planes;
        }

        #endregion

        #region Flight Search

        #endregion

        #region Utils

        public void InitializeDataBase()
        {
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = File.ReadAllText("../../../SQL/Install.sql");

            try
            {
                command.ExecuteReader();
            }
            catch (Exception)
            {
                connection.Close();
            }
        }

        public void DropTables()
        {
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = File.ReadAllText("../../../SQL/Uninstall.sql");

            try
            {
                command.ExecuteReader();
            }
            catch (Exception)
            {
                connection.Close();
            }
        }

        private static void AddSqlParameter(SqlCommand command, String name, SqlDbType type, object value)
        {
            var parameter = new SqlParameter
            {
                ParameterName = name,
                SqlDbType = type,
                Value = value
            };

            command.Parameters.Add(parameter);
        }

        #endregion
    }
}
